import pandas as pd
import json


def process_csv_excel_data(csv_file_path, excel_file_path):
    """
    处理CSV和Excel文件，根据条件过滤和匹配数据

    Args:
        csv_file_path: CSV文件路径
        excel_file_path: Excel文件路径

    Returns:
        list: 包含name和id的字典列表
    """

    # 1. 读取第一个CSV文件
    print("正在读取CSV文件...")
    df_csv = pd.read_csv(csv_file_path)

    # 2. 过滤掉subject等于"政治"的数据
    print("过滤政治相关数据...")
    df_filtered = df_csv[df_csv['subject'] != '政治'].copy()
    print(f"过滤后剩余 {len(df_filtered)} 条记录")

    # 3. 对planDescriptions字段进行去重（整个字段去重，不分割）
    print("对planDescriptions字段进行去重...")

    # 获取所有不重复的planDescriptions
    unique_plan_descriptions = df_filtered['planDescriptions'].dropna().drop_duplicates()
    print(f"去重后共有 {len(unique_plan_descriptions)} 个不同的planDescription")

    # 4. 读取第二个Excel文件
    print("正在读取Excel文件...")
    df_excel = pd.read_excel(excel_file_path)

    # 5. 处理每个去重后的planDescription
    print("进行数据匹配...")
    matched_results = []

    for plan_desc in unique_plan_descriptions:
        # 将planDescription按逗号分割
        individual_plans = [plan.strip() for plan in str(plan_desc).split('、') if plan.strip()]

        # 收集所有匹配的id
        all_matched_ids = []

        for plan in individual_plans:
            # 在Excel文件中查找匹配的name
            matching_rows = df_excel[df_excel['name'] == plan]

            if not matching_rows.empty:
                # 收集匹配的id
                matched_ids = [str(row['id']) for _, row in matching_rows.iterrows()]
                all_matched_ids.extend(matched_ids)

        if all_matched_ids:
            # 用逗号连接所有id
            id_string = ','.join(all_matched_ids)

            matched_results.append({
                "name": plan_desc,  # 保留原始的完整planDescription
                "id": id_string  # 用逗号分隔的id字符串
            })

    print(f"总共处理了 {len(matched_results)} 个planDescription")

    return matched_results


def main():
    # 文件路径（请根据实际情况修改）
    csv_file_path = "D:\\project_file\\诊断式学习\\enhanced_dialog_paths_1.csv"  # 替换为你的CSV文件路径
    excel_file_path = "D:\\project_file\\诊断式学习\\最新完整生产模板计划\\生产模板计划062615点完整.xlsx"  # 替换为你的Excel文件路径

    try:
        # 处理数据
        results = process_csv_excel_data(csv_file_path, excel_file_path)

        # 输出结果
        print("\n匹配结果:")
        print(json.dumps(results, ensure_ascii=False, indent=2))

        # 可选：保存结果到文件
        with open('matched_results.json', 'w', encoding='utf-8') as f:
            json.dump(results, f, ensure_ascii=False, indent=2)

        print(f"\n结果已保存到 matched_results.json 文件")

    except FileNotFoundError as e:
        print(f"文件未找到: {e}")
    except Exception as e:
        print(f"处理过程中出现错误: {e}")


if __name__ == "__main__":
    main()

